package com.biosan.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.xml.XMLSerializer;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;
import org.jdom.output.XMLOutputter;
import org.junit.Test;

import com.biosan.DaoTnterface.JDataSet;

public class testdb
{
    public static void main(String[] args) throws Exception
    {

        // System.out.println(aa.getCount());

        // JSONArray jsonArray = JSONArray.fromObject(aa);
        // System.out.println(BiosanUtils.ConvertToJson(BiosanUtils.getMapForsqlserver(
        // "SELECT TOP 1 KPXXXH AS Expr1, JBXXXH AS Expr2, KPBH AS Expr3, TXM AS Expr4 FROM T_KPXX", null)));
        // System.out.println(BiosanUtils.Md5("biosan"));
        // String sqlString2="update SMSsend set mobile='13777574896'";
        // String sqlString="insert into SMSsend (mobile,content,SendTime) values (?,?,?)";
        // Object params[]={mobile,Messagecontent,date};
        // BiosanUtils.executeUpdateForsqlserver(sqlString2, null);
        // BiosanUtils.getListMapForsqlserver(" select * from smssend ",null);//(sqlString2, null);
        // BiosanUtils.SendMessage("1377574896", "发送成功",BiosanUtils.StringToSqlDate("2012-12-12", "yyyy-mm-dd"));

        // System.out.println(BiosanUtils.JsonToXml("{\"name\":\"发生\",\"bool\":true,\"int\":1}"));

        // testdb test = new testdb();
        // test.readXML();

        // ExcellUtils excellUtils=new ExcellUtils();
        // List<Map<String, Object>> ls= excellUtils.ReadExcell("d:/Book1.xls");
        // System.out.println(ls);
        //        
        //        
        // //
        // SAXBuilder builder = new SAXBuilder();
        // Document document = builder.build("src/po.xml");
        // Element root = document.getRootElement();
        //        
        // List list = root.getChildren();
        // Iterator it = list.iterator();
        // for(int i = 0; i < list.size(); i++) {
        // Element e = (Element) it.next();
        // System.out.println("=============="+e.getAttributeValue("id"));
        // if(Integer.parseInt(e.getAttributeValue("id")) == 1) {
        // e.getChild("username").setText("wuchao");
        // e.getChild("password").setText("jiayou");
        //          
        // }
        // }
        // XMLOutputter output = new XMLOutputter();
        // output.output(document,new FileOutputStream("src/po.xml"));
        //

        // String sqlString = "insert into SMSsend (mobile,content,SendTime) values ('13777574896','test',getdate())";

        // System.out.println( BiosanUtils.ConvertToJson(BiosanUtils.getListMapForsqlserver("select * from  dbo.test", null)));
        // int i= BiosanUtils.executeUpdateForsqlserver(sqlString, null);
        // System.out.println(i);
        String params0 = "1300611000";
        QueryRunner qRunner = new QueryRunner();
        String sQuery0 = "SELECT  b.requisition_id, " + "a.csd as hos0," + "convert(varchar(10),a.csny,120) as childbirthday ,"
                + "a.lxrdh as mobile," + "a.lxr as domicile, " + "a.jtdz as house_address," + "a.xm as patientname,"
                + "convert(varchar(10),a.csny,120) as birthday"
                + " FROM setyy.dbo.asy a ,setyy.dbo.his_requisition b where   a.zyh = b.outpatient_id and " + " b.requisition_id like '" + params0
                + "%'";
        Connection conn = getsqlConnection("jdbcUrl0", "user0", "password0");
        Map<String, Object> mapoutpatient = qRunner.query(conn, sQuery0, new MapHandler());
        // BiosanUtils.getMapForsqlserver(sQuery0, null);
        System.out.println(mapoutpatient);
        if (mapoutpatient != null)
        {
            int collectid = 0;
            String collectnumber = "";
            String hoscode = "";
            if (mapoutpatient.get("hos0") != null)
            {

                Object mappatient = BiosanUtils.getSingle("select max(collectnum) from  ground_tp_create_data where hos like '%"
                        + mapoutpatient.get("hos0") + "%'", null);
                if (mappatient != null)
                {
                    collectnumber = mappatient.toString();
                    System.out.println("collectnumber:" + mapoutpatient.get("hos0") + collectnumber);
                }

                Map<String, Object> mappart = BiosanUtils.getMap(
                        "select substr(hos,0,instr(hos,'|')-1) as code,area_ul,hos from  ground_tp_create_data where hos like '%"
                                + mapoutpatient.get("hos0") + "%'", null);
                if (mappart != null)
                {
                    hoscode = mappart.get("code").toString();// 6位
                    System.out.println(hoscode);
                    System.out.println(collectnumber);
                    collectnumber = collectnumber.replaceAll(hoscode, "");//
                    System.out.println("collectnumber" + collectnumber);
                    if (collectnumber == "")
                        collectnumber = "0";
                    if (collectnumber.length() < 8)
                    {
                        collectid = Integer.valueOf(collectnumber) + 1;
                    }
                    else
                    {

                        collectid++;
                    }

                    mapoutpatient.put("HOS", mappart.get("hos").toString());

                    mapoutpatient.put("AREA_URL", mappart.get("area_ul").toString());

                    mapoutpatient.put("COLLECTNUM", hoscode + String.format("%07d", collectid));
                }
            }

        }
        System.out.println(BiosanUtils.ConvertToJson(mapoutpatient));

        conn.close();
    }

    public static Connection getsqlConnection(String jdbcUrl, String user, String password)
    {

        String className = null;
        String url = "";
        String name = "";
        String ps = "";
        Connection sqlconn = null;
        try
        {
            className = BiosanUtils.getFileNode("/sqlserver.properties", "driverClass");
            url = BiosanUtils.getFileNode("/sqlserver.properties", jdbcUrl);
            name = BiosanUtils.getFileNode("/sqlserver.properties", user);
            ps = BiosanUtils.getFileNode("/sqlserver.properties", password);
            try
            {
                Class.forName(className);
            }
            catch (ClassNotFoundException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

            try
            {

                sqlconn = DriverManager.getConnection(url, name, ps);

            }
            catch (SQLException e)
            {
                // TODO Auto-generated catch block

            }

        }
        catch (IOException e1)
        {

        }

        return sqlconn;
    }

    public void readXML()
    {
        // try
        // {
        //
        // Element el = BiosanUtils.readXML("D:\\workspace\\workspace2\\test.xml");
        // System.out.println(el);

        // List<Element> ls = el.getChildren("database");
        // System.out.println(ls);
        // Iterator it = ls.iterator();

        // while (it.hasNext())
        // {
        // Element sub_e = (Element) it.next();
        // Element sub_e_2 = sub_e.getChild("access2");
        // Element sub_e_3 = sub_e.getChild("access3");
        // Element sub_e_4 = sub_e.getChild("access4");
        // Element sub_e_5 = sub_e.getChild("access5");
        // System.out.println(sub_e_2.getName() + " " + sub_e_3.getText() + " " + sub_e_4.getText() + " " + sub_e_5.getText());
        // }
        // for (Element node : ls)
        // {
        // System.out.println("==" + node.getChild("access2"));
        // }

        // SAXBuilder sb2 = new SAXBuilder();
        // Document doc2 = sb2.build(new FileInputStream("D:\\快盘\\公司项目\\workspacefor Eclipse\\XmlTest\\src\\test1.xml"));
        // Element el2 = doc2.getRootElement();
        // System.out.println("==" + el2.getChild("AnalyteName").getText());
        // System.out.println("==" + el2.getChild("PlateMapName").getText());
        // System.out.println("==" + el2.getChild("TraversalOrder").getChild("OrderIndex").getText());
        //
        // List ls3 = el2.getChild("Wells").getChildren("Well");
        // Iterator it3 = ls3.iterator();
        // while (it3.hasNext())
        // {
        // Element sub_e3 = (Element) it3.next();
        // Element sub_e_2 = sub_e3.getChild("Index");
        // Element sub_e_3 = sub_e3.getChild("SampleBarcode");
        // Element sub_e_4 = sub_e3.getChild("WellType").getChild("Type");
        // Element sub_e_5 = sub_e3.getChild("IsBad");
        // System.out.println(sub_e_2.getText() + "#" + sub_e_3.getText() + "#" + sub_e_4.getText() + "#" + sub_e_5.getText());
        // }
        //
        // }
        // catch (Exception e)
        // {
        // e.printStackTrace();
        // }

        // System.out.println(IOUtil.getFileslist("d://test", "xml"));
        System.out.println(IOUtil.renameTofile("d://biosan22.json2", "d://biosan.qq"));
    }

    // list--json-xml
    public void testlistTojson() throws Exception
    {

        // System.out.println("ArrayList"+BiosanUtils.ConvertToJson(stuList));
        // JSONArray jsonArray = JSONArray.fromObject(stuList);
        // System.out.println(stuList);
        Map m = new HashMap()
        {
            {
                put("JSon", "HelloWorld");
                put("Flex", "Ok");
            }
        };
        System.out.println(m);
        String[] aa = { "2" };

        // BiosanUtils.JsonToXml(jsonstring)
        // BiosanUtils.ConvertToJson(strjava)
        // List<Map<String, Object>>
        // list1=BiosanUtils.getDb().query("select *  from GROUND_TB_PATIENT",
        // new MapListHandler());

        // System.out.println(JsonUtils.ConvertToJson(aa));
        // System.out.println(JsonUtils.ConvertToJson(m));
        // System.out.print(JsonUtil.JsonToXml2(jsonArray.toString()));
        // DButils<Book> aa=new DButils<Book>();
        // aa.find(sql, params, viewName, executeCall)
        // System.out.println(BiosanUtils.ConvertToJson(BiosanUtils.getDb().query("select *  from GROUND_TB_PATIENT",
        // new MapListHandler())));
        System.out.println("======================");
        String[] dd = { "样本", "样本id" };
        Map map1 = new HashMap();
        map1.put("1", "aa");
        // System.out.println(BiosanUtils.ConvertToJson(map1));
        // System.out.println(BiosanUtils.ConvertToJson(BiosanUtils.getDb(1).getJDataSet("select *  from GROUND_TB_PATIENT",null,true,false)));
        // JDataSet
        // jSet2=BiosanUtils.getDb(1).getJDataSet("select *  from GROUND_TB_PATIENT",null,true,false);
        // System.out.println(jSet2.getCell(0, 0)+jSet2.getCell(0, 1));
        // System.out.println(BiosanUtils.ConvertToJson(jSet2));
        // System.out.println(list1);
        // BiosanUtils.Toexcell("C:/biosan2.xls","样本清单",dd,list1);
        String str = "<Ipf>\n" + "  <header>\n" + "    <transactionId>2011011124586</transactionId>\n" + "    <userName>hzbs</userName>\n"
                + "<passWord>hzbs2011</passWord>\n" + "  </header>\n" + "  <body>\n" + "<mobile>13777574896</mobile>\n" + "<content>test</content>\n"
                + "<packageType>0</packageType>\n" + "  </body>\n" + "</Ipf>";

        // Response<Book> rs=new Response<Book>();
        // Book book1=new Book();
        // book1.setBook_id(1);
        // book1.setBook_name("bookname");
        // ArrayList<Book> aa3= rs.getlist();
        // aa3.add(book1);

        // System.out.println(aa3);
        // System.out.println(BiosanUtils.ConvertToJson(aa3));

        // JDataSet
        // aa2=BiosanUtils.getDb(1).getJDataSetAsPager("OPERATEDATE","ground_tb_sample",
        // 2, 20, "PATIENTID", "and PATIENTID>0");
        // BiosanUtils.getDb(1).getResultSetAsPager("OPERATEDATE","ground_tb_sample",
        // 2, 20, "PATIENTID", "and PATIENTID>0");
        System.out.println("========================");

        // BiosanUtils.getDb(1).returnResultSet("select OPERATEDATE from ground_vw_patient_search_nb2 where rownum<=10",
        // null, false);
        // System.out.println(BiosanUtils.ConvertToJson(BiosanUtils.getDb(1).returnResultSet("select * from ground_tb_patient",
        // null, false)));

        // test();

        // System.out.println("共："
        // + (String.valueOf(BiosanUtils
        // .getSingle("select sum(book_ID) from book"))));
        // System.out
        // .println("共：" + BiosanUtils.getDb(1).getRowCount("from book"));
        // System.out.println(BiosanUtils.ConvertToJson(BiosanUtils.getListMapAsPager("to_char(birthday,'yyyy-mm-dd hh24:mi:ss') as birthday",
        // "ground_tb_patient", 1,10,"OPERATEDATE", "")));
        // System.out.println("ResultSet："
        // + BiosanUtils.getDb(1).getResultSet("select * from book", null,
        // false));
        // System.out.println("ResultSetjson："+BiosanUtils.getDb(1).getSqlToJson("select * from book",
        // null, false));
        // BiosanUtils.getDb(1).getJSONArrayAsPager(columnName, tableName, currentPage, pageSize, orderBy, andwhere)

        // System.out.println("conn5:"+BiosanUtils.getDb(1).conn);
        // ResultSet aa2 = BiosanUtils.getDb(1).getResultSet("select * from book",
        // null, false);
        // while (aa2.next())
        // {
        //
        // System.out.println("ResultSet" + aa2.getString(1));
        // }
        // Date d=new Date();
        // int i=BiosanUtils.executeUpdate("update book set book_name='yzqbook' where book_name='book2'", null);
        // System.out.println("i-----"+i);
        // System.out.println(BiosanUtils.StringToLongDate("2010-1-2"));

        // String json="[{'key':'20'},{'key':30}]";
        // String json="[{'D':'0001','A':'hsis002206027137813','B':'1','C':''}]";
        //		
        // JSONArray arr=JSONArray.fromObject(json);
        // for(int j=0;j<arr.size();j++)
        // {
        // JSONObject o=(JSONObject) arr.get(j);
        // // System.out.println("json:"+o.get("key"));
        // System.out.println("json:"+o.get("A"));
        // // IOUtil.appendline("D:/yzq.log", "我是新的一行",true,true);
        //		  
        // }
        // System.out.println(BiosanUtils.getThreadSleep("/dbcp.properties","threadSleep"));
        // // System.out.println(IOUtil.readline("D:/yzq.log"));
        //		 
        // System.out.println(BiosanUtils.getDb(1).getJDataSetAsPager("OPERATEDATE","ground_tb_sample",
        // 2, 20, "PATIENTID", "and PATIENTID>0"));

        // BiosanUtils.getDb("/sqlserver.properties").query("SELECT TOP 1 KPXXXH AS Expr1, JBXXXH AS Expr2, KPBH AS Expr3, TXM AS Expr4 FROM T_KPXX",
        // new MapListHandler(), null);

        // System.out.println(BiosanUtils.ConvertToJson(BiosanUtils.getMapForsqlserver(
        // "SELECT TOP 1 KPXXXH AS Expr1, JBXXXH AS Expr2, KPBH AS Expr3, TXM AS Expr4 FROM T_KPXX", null)));

    }

    @Test
    public void test() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, IOException
    {
        for (int i = 0; i < 1000; i++)
        {

            // String select_sample_id = "select sampleid from ground_vw_key_mapping where workkey='hsis002207706139455'";
            // Object sampleId = BiosanUtils.getSingle(select_sample_id);
            // System.out.println(i+"p"+sampleId);

            Connection conn;
            Statement stmt;
            ResultSet rs;

            String driver = "oracle.jdbc.driver.OracleDriver";

            Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
            /*
             * String url = "jdbc:microsoft:sqlserver://192.168.16.254:1433;DatabaseName=wcdbsz"; String url =
             * "jdbc:microsoft:sqlserver://172.25.220.25:1433;DatabaseName=wcdbsz"; String user = "xiaoyu"; String password = "xiaoyu2010";
             */

            String url = "jdbc:oracle:thin:@192.168.16.251:1521:orcl";
            String user = "neonatal";
            String password = "biosan11";
            conn = DriverManager.getConnection(url, user, password);

            // stmt = conn.createStatement();
            //                        
            // String sql_select_department = "select sampleid from ground_vw_key_mapping where workkey='hsis002207706139455' ";
            //                        
            // rs = stmt.executeQuery(sql_select_department);
            // while (rs.next())
            // {
            // String dep = rs.getString("sampleid");
            // System.out.println(i+"=2222="+dep);
            // }
            //                                    
            // rs.close();
            // stmt.close();
            // conn.close();
            //                     

            String update_sdb = "update ground_tb_sam_dep_business set business=? where sampleid=? and departmentid=?";
            Object[] business = { "1", "2", "11" };
            // // BiosanUtils.executeUpdate(update_sdb, business);
            // // BiosanUtils.getDb().update(conn, update_sdb);
            //            
            QueryRunner run = new QueryRunner();
            run.update(conn, update_sdb, business);
            conn.close();
            // BiosanUtils.getDb(1).execute(update_sdb, business, false);
            System.out.println(i + "ttttw===");
            conn = DriverManager.getConnection(BiosanUtils.getFileNode("/dbcp.properties", "jdbcUrl"), BiosanUtils.getFileNode("/dbcp.properties",
                    "user"), BiosanUtils.getFileNode("/dbcp.properties", "password"));
            run.update(conn, update_sdb, business);
            conn.close();
            System.out.println(i + "lyuuuuuuuuuuuuuuuuu===");

        }

    }

    public class Sample
    {
        String sampleid = ""; // 样本编号
        String samplestring = "";

        public String getSampleid()
        {
            return sampleid;
        }

        public void setSampleid(String sampleid)
        {
            this.sampleid = sampleid;
        }

        public String getSamplestring()
        {
            return samplestring;
        }

        public void setSamplestring(String samplestring)
        {
            this.samplestring = samplestring;
        }

        public String getPatientname()
        {
            return patientname;
        }

        public void setPatientname(String patientname)
        {
            this.patientname = patientname;
        }

        public String getBirthday()
        {
            return birthday;
        }

        public void setBirthday(String birthday)
        {
            this.birthday = birthday;
        }

        public String getSamplebarcode()
        {
            return samplebarcode;
        }

        public void setSamplebarcode(String samplebarcode)
        {
            this.samplebarcode = samplebarcode;
        }

        public String getDepartmentname()
        {
            return departmentname;
        }

        public void setDepartmentname(String departmentname)
        {
            this.departmentname = departmentname;
        }

        public String getKey()
        {
            return key;
        }

        public void setKey(String key)
        {
            this.key = key;
        }

        public String getLururen()
        {
            return lururen;
        }

        public void setLururen(String lururen)
        {
            this.lururen = lururen;
        }

        String patientname = "";
        String birthday = "";
        String samplebarcode = "";
        String departmentname = ""; // 增加部门名称
        String key = "";
        String lururen = ""; // 录入人

    }

}
